System Manager Tables

Note: Rows and/or Tables marked with an asterisk (*) are added to Traverse Global. Rows and/or Tables marked with a negative asterisk (-*) refer to tables and/or rows that are removed in Traverse Global.

Column Name Column Type Length Default Value Description Enumeration
ActivityId uniqueidentifier 16      
Comments varchar 50      
Description varchar 50      
FunctionId uniqueidentifier 16      
PostRun pPostRun 14      
RunTime datetime 8      
ts timestamp 8      
UserId pUserID 20      
WrkStnId pWrkStnID 20      

tblSmApp -- SM Applications table

Column Name Column Type Length Default Value Description Enumeration
AppID nvarchar 2      
Notes nvarchar 510      
Column Name Column Type Length Default Value Description Enumeration
Comment nvarchar max      
Description nvarchar 100      
EnteredBy pUserID 20      
EntryDate datetime 8      
ExpireDate datetime 8      
FileName nvarchar max      
Id int 4      
Keywords nvarchar 510      
LinkKey nvarchar 510      
LinkType varchar 10      
Priority tinyint 1 0 Priority 0 = Regular, 1 = High Priority
Status tinyint 1 0 Status 0 = Public, 1 = Private
ts timestamp 8      
CF xml -1      
Column Name Column Type Length Default Value Description Enumeration
BankId pBankID 10      
Desc nvarchar 35      
Name nvarchar 40      
Contact nvarchar 30      
Addr1 nvarchar 30      
Addr2 nvarchar 60      
City nvarchar 30      
Region nvarchar 10      
Country pCountry 6      
PostalCode nvarchar 10      
IntlPrefix nvarchar 6      
Phone nvarchar 15      
FAX nvarchar 15      
OurAcctNum pGlAcct 40      
CurrencyId pCurrency 6      
GlCashAcct pGlAcct 40      
Email pEmail 255      
Internet pWeb 255      
CheckLayout tinyint 1 0 Check Layout 0 = Check / stub / stub, 1 = Stub / check / stub, 2 = Stub / stub / check, 3 = Check / extended stub
CheckFormat tinyint 1 0 Check Format 0 = Format 1 (Current check layout (default)), 1 = Format 2 (Canadian compliant)
GLAcctBal pDec 13 0    
LastStmtBal pDec 13 0    
LastStmtDate datetime 8      
ReconsImpId pItemID 24      
AcctType tinyint 1 0 Bank Account Type 0 = General, 1 = Credit Card
RoutingCode nvarchar 9      
MICR bit 1 0    
NextCheckNo pCheckNum 10      
APPosPay nvarchar 24      
PAPosPay nvarchar 24      
CcExpire datetime 8      
VendorId pVendorID 10      
RoutingFraction nvarchar 13      
NextVoucherNo pCheckNum 10      
FilingCode nvarchar 1      
FRBRoutingCode nvarchar 9      
SecurityCode nvarchar 94      
SecurityCodePadLength tinyint 1      
ACHExcludeOffset bit 1      
ACHNextBatchNumber int 4      
ACHLastPayDate datetime 8      
ACHFilePath nvarchar 255      
ACHFileName nvarchar 255      
ACHExecute nvarchar 255      
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
BatchId pBatchID 6      
CreateDate datetime 8      
Descr varchar 50      
FunctionId varchar 10      
Lock bit 1 0    
LockBy pUserID 20      
LockDate datetime 8      
Permanent bit 1 0    
ProcessId uniqueidentifier 16      
Status xml -1      
ts timestamp 8      
CF xml -1      

tblSmCompInfo -- SM Company Information table

tblSmConfig -- SM Configuration table

tblSmConfigDep -- SM Dependency Configuration table

Column Name Column Type Length Default Value Description Enumeration
ConfigValueRef int 4      
ConfigRef int 4      
RoleId nvarchar 255      
ConfigValue nvarchar 255      

tblSmCountry -- SM Country table

tblSmCurrency -- SM Currency table

Column Name Column Type Length Default Value Description Enumeration
CurrId pCurrency 6      
CurrDesc pDescription 255      
CurrDecPlaces tinyint 1 0    
CurrSymbol nvarchar 6      
GLAcctUnrealGain pGlAcct 40      
GLAcctUnrealLoss pGlAcct 40      
GLAcctRealGain pGlAcct 40      
GLAcctRealLoss pGlAcct 40      
SettlementVariance pDec 13      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
Definition xml -1      
FieldName nvarchar 100      
Id int 4      
Column Name Column Type Length Default Value Description Enumeration
EntityId nvarchar 510      
FieldId int 4      
Id int 4      
Layout xml -1      
Column Name Column Type Length Default Value Description Enumeration
Id int 4      
ContactId nvarchar 20      
FormId nvarchar 10      
ContactType tinyint 1   Contact Type 0 = Customer, 1 = Vendor
DeliveryType tinyint 1 0 Delivery Type 0 = Paper, 1 = E-mail, 2 = Fax
DeliveryName nvarchar 30      
DeliveryDestination pEmail 255      
DeliveryNote nvarchar 255      
EmailAttachmentFormat tinyint 1      
IncludePaperCopy bit 1 0 Email Attachment Format 0 = Snapshot, 1 = Image, 2 = RTF, 3 = PDF
CF xml -1      
Column Name Column Type Length Default Value Description Enumeration
FormID nvarchar 10      
SMTPID bigint        
Subject nvarchar 255      
EmailBody nvarchar max      
EmailAttachment varbinary max      
SendAsHtml bit 1      
FaxCoverPage varbinary max      
AutoSend bit 1      
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint        
FormID nvarchar 10      
FunctionID Uniqueidentifier        
Subject nvarchar 255      
DeliveryDestination pEmail 255      
DeliveryType tinyint 1      
DeliveryName nvarchar 30      
EmailBody nvarchar max      
EmailAttachment varbinary max      
RunTime datetime        
SentTime datetime        
ActivityId guid        
ContactId nvarchar 20      
UserID pUserID        
HostID pUserID        
DocumentNo nvarchar 255      
KeyFieldName nvarchar 255      
KeyFieldValue nvarchar 255      
SourceId nvarchar 255      
ModifiedBy pUserID        
ModifiedTime datetime        
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
EmployeeId pEmpID 11      
LastName nvarchar 20      
FirstName nvarchar 15      
MiddleInit nvarchar 1      
AddressLine1 nvarchar 30      
AddressLine2 nvarchar 60      
ResidentCity nvarchar 30      
ResidentState nvarchar 2      
ZipCode nvarchar 12      
CountryCode pCountry 6      
SocialSecurityNo nvarchar 510      
PhoneNumber nvarchar 13      
WorkPhoneNo nvarchar 13      
WorkExtension nvarchar 5      
BirthDate datetime 8      
EmergencyContact nvarchar 20      
ContactWorkPhone nvarchar 13      
ContactHomePhone nvarchar 13      
ContactRelation nvarchar 18      
WorkEmail pEmail 255      
HomeEmail pEmail 255      
Internet pWeb 255      
CF xml -1      
ts timestamp 8      
Status tinyint 1   Employee Status 0 = Active, 1 = Inactive

tblSmExchRate -- SM Exchange Rate table

Column Name Column Type Length Default Value Description Enumeration
ID bigint 8      
Type tinyint 1     0 = Incremental, 1 = Period
EffectiveDate datetime 8 getdate()    
FiscalYear smallint 2      
FiscalPeriod smallint 2 0    
Notes nvarchar max      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint 8      
ExchangeRateID bigint 8     0 = Incremental, 1 = Period
Source pCurrency 6      
Destination pCurrency 6      
Rate pDec 13 0    
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint 8      
TransType nvarchar 10      
TransKey nvarchar 255      
Source pCurrency 6      
Destination pCurrency 6      
Rate pDec 13 0    
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint 8      
ParentID bigint 8      
ActType tinyint 1     0 = RecurAuth, 1 = Authorize, 2 = Capture, 3 = Sale, 4 = Return, 5 = Refund, 6 = Payment Method Maint, 7 = Sale Void, 8 = Auth Void, 9 = Sale Credit Void
ActStatus tinyint 1     0 = Pending, 1 = Approved, 2 = Declined, 3 = Failed
SourceStatus tinyint 1     0 = Pending, 1 = Confirmed, 2 = Completed, 3 = Voided
EntryDate datetime 8 getdate()    
TransDate datetime 8 getdate()    
PmtMethodID nvarchar 10      
ExPmtMethodID bigint 8      
CustomerID pCustId        
CustomerReference nvarchar 255      
FiscalYear smallint 2      
FiscalPeriod smallint 2      
SourceID uniqueidentifier        
SourceType tinyint 1 1   0 = Invoice, 1 = Payment, 2 = Credit, 3 = Proforma
SourceDocument nvarchar 255   Document number for source transaction (Order No/Invoice/Credit Memo)  
SourceReference nvarchar 255      
CustomerPaymentAlias nvarchar 255      
ServiceID int        
AuthorizationCode nvarchar 255   Authorization code returned by provider  
TransactionID nvarchar 255   Payment provider's transaction identifier  
PaymentDate datetime 8 getdate()    
PaymentAmount pDecimal 13 0    
CurrencyID pCurrency 13      
ResponseCode nvarchar 10   Response code returned from provider  
ResponseMessage nvarchar max   Response message returned from provider  
Response nvarchar max   Entire response data string from payment provider  
WebPostData nvarchar max   API Post data  
RequestData nvarchar max   Request data from source transactions  
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint 8      
CustomerID pCustId        
Status tinyint 1     0 = Active, 1 = Inactive, 2 = Deleted
DefaultYn bit 1 0    
EntryDate datetime 8 getdate()    
UpdatedDate datetime 8   optional date flag to identify updated entries  
CardHolder nvarchar 30      
ServiceID int        
Description nvarchar 255      
MaskValue nvarchar 4      
ExpirationDate nvarchar 6      
CardID nvarchar 255      
Response nvarchar max   entire response data string from payment provider  
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
FormId nvarchar 15      
NextNum int 4 0    
LockBy pUserID 20      
WrkStnID pWrkStnID 20      
LockDate datetime 8 getdate()    
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
Id int 4      
FunctionId nvarchar 10      
GlYear smallint 2      
Period smallint 2      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint 8      
Description pDescription 50      
FreightPaidBy tinyint 1 0   0 = Shipper, 1 = Receiver
UseCalculateFreight bit 1 0    
FreightAdjustType tinyint 1 0   0 = Percent, 1 = Amount, 2 = Flat
FreightAdjustAmount pDecimal 13 0    
CF xml -1      
ts timestamp 8      

tblSmExportLayout -- SM Export Layout table

tblSmExportLayoutField -- SM Export Field Layout table

tblSmExportLayoutSection -- SM Export Section Layout table

tblSmImportLayout -- SM Import Layout table

tblSmImportLayoutField -- SM Import Field Layout table

tblSmImportMap -- SM Import Map table

Column Name Column Type Length Default Value Description Enumeration
CurrencyID pCurrency 6      
GlAcctUnrealGain pGlAcct 40      
GlAcctUnrealLoss pGlAcct 40      
GlAcctRealGain pGlAcct 40      
GlAcctRealLoss pGlAcct 40      
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ItemCode pItemID 24      
Desc nvarchar 35      
GLAcctExp pGlAcct 40      
GLAcctSales pGlAcct 40      
GLAcctCogs pGlAcct 40      
GLAcctInv pGlAcct 40      
TaxClass tinyint 1 0    
Units pUom 5      
UnitCost pDec 13 0    
UnitPrice pDec 13 0    
AddnlDesc text 16      
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ActivityId uniqueidentifier 16      
Description varchar 50      
Log varbinary max      
LogId int 4      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
InfoRef int 4      
InfoID nvarchar 50      
InfoValue nvarchar 255      
ts timestamp 8      

tblSmMapProgram -- SM Map Program table

tblSmMenu-- SM Menu table

tblSmMenuCustom-- SM Custom Menu table

tblSmMenuDescr-- SM Menu Description table

Column Name Column Type Length Default Value Description Enumeration
BegDate datetime 8      
ClosedAP bit 1      
ClosedAR bit 1      
ClosedBM bit 1      
ClosedBR bit 1      
ClosedF4 bit 1      
ClosedF5 bit 1      
ClosedFA bit 1      
ClosedGL bit 1      
ClosedIN bit 1      
ClosedJC bit 1      
ClosedMB bit 1      
ClosedMF bit 1      
ClosedMP bit 1      
ClosedMR bit 1      
ClosedPA bit 1      
ClosedPC bit 1      
ClosedPO bit 1      
ClosedSO bit 1      
EndDate datetime 8      
GlPeriod smallint 2      
GlYear smallint 2      
Notes varchar 255      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint        
Description nvarchar 255      
Server nvarchar 255      
Port int        
EnableSSL bit        
UserName nvarchar 255      
Password nvarchar max      
SenderName nvarchar 255      
SenderEmail nvarchar 255    
ReplyTo nvarchar 255      
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
CopyText nvarchar 100      
DeviceName nvarchar 510 n'{default}'    
Disabled bit 1      
FormId nvarchar 100      
HostId pWrkStnID 20 '{all}'    
PrintOrder tinyint 1 -1    
SeqNum int 4      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
Descr pDescription 255      
Id varchar 10      
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
Id bigint 8      
ScheduleId varchar 10      
BeginDate datetime 8      
DayValue tinyint 1   Day Value 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday
Descr pDescription 255      
EndDate datetime 8      
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
Id bigint 8      
ParentId bigint 8      
ResourceType tinyint 1     4 = Technician
ResourceId nvarchar 255      
EventType tinyint 1     0 = Resource Unavailable, 1 = Resource Available
PatternType tinyint 1     0 = Normal, 1 = Recurring Pattern, 2 = Occurrence, 3 = Changed Occurrence, 4 = Deleted Occurrence
OccurrenceId int 4      
StartDate datetime 8      
StartTime int 4      
TimeZone smallint     +/- UTC offset in minutes for current time zone  
Duration int 4     0 = All Day; else duration in seconds
Subject nvarchar 255      
Description nvarchar max      
RecurType tinyint 1     0 = None, 1 = Daily, 2 = Weekly, 3 = Monthly, 4 = Yearly
RecurFrequency smallint     # of RecurType units between occurrences  
RecurInstance tinyint 1     0 = Specific, 1 = First, 2 = Second, 4 = Third, 8 = Fourth; 16 = Last
RecurDay tinyint 1   Day # of month, bitmasked days of week depending on instance type bitmask: 1 = Sun, 2 = Mon, 4 = Tue, 8 = Wed, 16 = Thurs, 32 = Fri, 64 = Sat, 128 = Weekday, 256 = Weekend
RecurMonth tinyint 1   month # of year when RecurType = Yearly, NA for other RecurTypes  
RecurStart datetime 8      
RecurEnd datetime 8   null for open-ended recurrence  
RecurCount int 4     <1 = Unused, >0 = # ends after # of recurrences
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
Id bigint 8      
ScheduleDateId bigint 8      
BeginTime int 4 0    
Duration int 4 0    
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint 8      
Description pDescription        
Contact nvarchar 30      
Attn nvarchar 30      
Address1 nvarchar 30      
Address2 nvarchar 60      
City nvarchar 30      
Region nvarchar 10      
Country nvarchar 6      
PostalCode nvarchar 10      
Phone nvarchar 15      
Fax nvarchar 15      
Email pEmail 255      
Internet pWeb 255      
SCAC nvarchar 4      
AccountNumber nvarchar 255      
Currency pCurrency        
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint 8   IDENTITY  
SourceID nvarchar 255      
TrackingNumber nvarchar 255      
TotalCharges pDecimal 13      
ActualWeight pDecimal 13      
VoidIndicator nvarchar 255      
EntryDate datetime 8 getdate()    
ShipDate datetime 8      
ShipVia nvarchar 20      
ShipZip nvarchar 10      
Status tinyint 1 0   0 = New, 1 = Completed
NumberOfPackage int        
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
TaxClassCode tinyint 1      
Desc nvarchar 35      
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
TaxClassRateID int 4      
TaxLocId pTaxLoc 10      
TaxClassCode tinyint 1      
RangeThru pDec 13      
TaxSales pDec 13 0    
TaxPurch pDec 13 0    
TaxRefund pDec 13 0    
TaxType tinyint 1 0 Tax Type 0 = Percent, 1 = Amount
CF xml -1      
Column Name Column Type Length Default Value Description Enumeration
TaxGrpID pTaxLoc 10      
Desc nvarchar 30      
ReportMethod tinyint 1 0 Reporting Method 0 = Combined, 1 = Separate
LevelOne pTaxLoc 10      
LevelTwo pTaxLoc 10      
LevelThree pTaxLoc 10      
LevelFour pTaxLoc 10      
LevelFive pTaxLoc 10      
CF xml -1      
ts timestamp 8      
TaxGroupType tinyint 1 0   0 = Regular, 1 = UseTax, 2 = Extended1, 3 = Extended2
Column Name Column Type Length Default Value Description Enumeration
LevelNo tinyint 1 0    
TaxGrpID pTaxLoc 10      
TaxLocID pTaxLoc 10      
Tax1 bit 1 0    
Tax2 bit 1 0    
Tax3 bit 1 0    
Tax4 bit 1 0    
Tax5 bit 1 0    
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
TaxLocId pTaxLoc 10      
Name nvarchar 30      
TaxLevel tinyint 1 -1 Tax Level 1, 2, 3, 4, 5
TaxAuthority nvarchar 2      
TaxId nvarchar 20      
TaxOnFreight bit 1 0    
TaxOnMisc bit 1 0    
GLAcct pGlAcct 40      
TaxRefAcct pGlAcct 40      
IgnoreExpAcctYn bit 1 0    
CF xml -1      
ts timestamp 8      
*CurrencyID pCurrency 6      
Column Name Column Type Length Default Value Description Enumeration
TaxLocId pTaxLoc 10      
TaxClassCode tinyint 1 0    
TaxableYn bit 1 -1    
SalesTaxPct pDec 13 0    
PurchTaxPct pDec 13 0    
RefundPct pDec 13 0    
TaxSales pDec 13 0    
NonTaxSales pDec 13 0    
TaxCollect pDec 13 0    
TaxPurch pDec 13 0    
NonTaxPurch pDec 13 0    
TaxCalc pDec 13 0    
TaxPaid pDec 13 0    
TaxRefund pDec 13 0    
ExpenseAcct pGlAcct 40      
CalcMethod tinyint 1   Calculation Method 0 = Total, 1 = Gradient
CF xml -1      
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
EntryNum int 4      
TaxLocId pTaxLoc 10      
TaxClassCode tinyint 1 0    
PostRun pPostRun 14      
SourceCode nvarchar 2      
LinkID nvarchar 15      
LinkIDSub nvarchar 15      
LinkIDSubLine int 4 0    
TransDate datetime 8      
GLPeriod smallint 2 0    
FiscalYear smallint 2 0    
TaxSales pDec 13 0    
NonTaxSales pDec 13 0    
TaxCollect pDec 13 0    
TaxPurch pDec 13 0    
NonTaxPurch pDec 13 0    
TaxCalcSales pDec 13 0    
TaxCalcPurch pDec 13 0    
TaxPaid pDec 13 0    
TaxRefund pDec 13 0    
CF xml -1      
*CurrencyID pCurrency 6      
TaxGroupType tinyint 1 0   0 = Regular, 1 = UseTax, 2 = Extended1, 3 = Extended2

tblSmTen99Format -- SM 1099 Format table

Column Name Column Type Length Default Value Description Enumeration
BatchId pBatchID 6      
FunctionId varchar 10      
LockDate datetime 8 getdate()    
ProcessId uniqueidentifier 16      
TransId varchar 255      
ts timestamp 8      
UserId pUserID 20      
ActivityId uniqueidentifier 16      
PostRun pPostRun 14      
RunTime datetime 8      
Description nvarchar 50      
Comments nvarchar 50      
WrkStnId pWrkStnID 20      
Column Name Column Type Length Default Value Description Enumeration
FunctionID nvarchar 10      
NextID int 4 -1    
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
SeqNum int 4      
SourceType smallint 2   Source Type 0 = NA, 3 = PC, 4 = SO, 5 = SD, 6 = MP Material, 7 = MP Subcontract, 8 = SR
SourceId nvarchar 255      
DestType smallint 2   Destination Type 0 = NA, 1 = PO Requisition, 2 = PO Transaction
DestId nvarchar 255      
DropShipYn bit 1 0    
ts timestamp 8      
Column Name Column Type Length Default Value Description Enumeration
ID bigint 8      
UserID int 4      
LinkType smallint 2   Link Type 1 = Customer, 2 = Vendor, 3 = Employee, 4 = Sales Rep
LinkID nvarchar 510      
ExtInfo xml -1      
ts timestamp 8      

tblSmUserPreference -- SM User Preferences table